package ro.xblue.translator;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.servlet.Constants;
/**
 * <p>
 * Title: Romanian AIDA
 * </p>
 * <p>
 * Description: :D application
 * </p>
 * <p>
 * Copyright: Copyright (comparator) 2003
 * </p>
 * <p>
 * Company: Romania Development Gateway
 * </p>
 * 
 * @author Mihai Popoaei, mihai_popoaei@yahoo.com, smike@intellisource.ro
 * @version 1.0-*
 * @version $Id: TranslatorDBAccessor.java,v 1.1 2004/10/24 23:37:02 mihaipostelnicu Exp $
 */
public class TranslatorDBAccessor extends DBPersistenceManager {
	private TranslatorDBAccessor(PersistenceToken token) {
		super(token);
	}
	public static TranslatorDBAccessor getManager(PersistenceToken token) {
		return new TranslatorDBAccessor(token);
	}
	public void insert(TransItem item) throws SQLException {
		if (item.getId() == -1) {
			item.setId(db_token.getIdgenerators().nextVal(
					Constants.IDGEN_TRANS_ITEMS));
		}
		Connection con = getConnection();
		try {
			PreparedStatement ps = con.prepareStatement(INSERT);
			ps.setLong(1, item.getId());
			ps.setString(2, item.getModule());
			ps.setString(3, item.getLabel());
			ps.setString(4, item.getLang());
			ps.setString(5, item.getText());
			ps.setString(6,item.getSource());
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
	}
	public TransItem[] getByModule(String module_name) throws SQLException {
		Connection con = getConnection();
		ArrayList items = new ArrayList();
		TransItem result[] = null;
		try {
			PreparedStatement ps = con.prepareStatement(GET_BY_MODULE);
			ps.setString(1, module_name);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				TransItem item = new TransItem();
				item.setModule(module_name);
				item.setId(rs.getLong(1));
				item.setLabel(rs.getString(2));
				item.setLang(rs.getString(3));
				item.setText(rs.getString(4));
				item.setUpdated(rs.getTimestamp(5));
				item.setSource(rs.getString(6));
				items.add(item);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
		if (items.size() > 0) {
			result = new TransItem[items.size()];
			items.toArray(result);
		}
		return result;
	}
	public String[] getModuleNames() throws SQLException {
		Connection con = getConnection();
		ArrayList items = new ArrayList();
		String[] result = null;
		try {
			PreparedStatement ps = con.prepareStatement(GET_MODULES);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				items.add(rs.getString(1));
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
		if (items.size() > 0) {
			result = new String[items.size()];
			items.toArray(result);
		}
		return result;
	}
	public boolean checkTranslatorTable() throws SQLException {
		Connection con = getConnection();
		try {
			PreparedStatement ps = con
					.prepareStatement("SELECT count(*) FROM iface_trans");
			ps.executeQuery().close();
			ps.close();
		} catch (SQLException sqlEx) {
			return false;
		} finally {
			con.close();
		}
		return true;
	}
	public TransItem getById(long id) throws SQLException {
		Connection con = getConnection();
		TransItem result = null;
		try {
			PreparedStatement ps = con.prepareStatement(GET_BY_ID);
			ps.setLong(1, id);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				result = new TransItem();
				result.setId(id);
				result.setModule(rs.getString(1));
				result.setLabel(rs.getString(2));
				result.setLang(rs.getString(3));
				result.setText(rs.getString(4));
				result.setUpdated(rs.getTimestamp(5));
				result.setSource(rs.getString(6));
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
		return result;
	}
	public TransItem[] getByModuleAndLabel(String module, String label)
			throws SQLException {
		Connection con = getConnection();
		ArrayList items = new ArrayList();
		TransItem result[] = null;
		try {
			PreparedStatement ps = con
					.prepareStatement(GET_BY_MODULE_AND_LABEL);
			ps.setString(1, module);
			ps.setString(2, label);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				TransItem item = new TransItem();
				item.setModule(module);
				item.setLabel(label);
				item.setId(rs.getLong(1));
				item.setLang(rs.getString(2));
				item.setText(rs.getString(3));
				item.setUpdated(rs.getTimestamp(4));
				item.setSource(rs.getString(5));
				//System.out.println( item.getLang() + ":" + item.getText() );
				items.add(item);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
		if (items.size() > 0) {
			result = new TransItem[items.size()];
			items.toArray(result);
		}
		return result;
	}
	public void updateByModuleLabelAndLang(TransItem ti) throws SQLException {
		Connection con = getConnection();
		TransItem item = null;
		try {
			PreparedStatement ps = con
					.prepareStatement(UPDATE_BY_MODULE_LABEL_AND_LANG);
			ps.setString(1, ti.getText());
			ps.setString(2, ti.getSource());
			ps.setString(3, ti.getModule());
			ps.setString(4, ti.getLabel());
			ps.setString(5, ti.getLang());
			
			ps.executeUpdate();
		
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
		
	}
	public TransItem getByModuleLabelAndLang(String module, String label,
			String lang) throws SQLException {
		Connection con = getConnection();
		TransItem item = null;
		try {
			PreparedStatement ps = con
					.prepareStatement(GET_BY_MODULE_LABEL_AND_LANG);
			ps.setString(1, module);
			ps.setString(2, label);
			ps.setString(3, lang);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				item = new TransItem();
				item.setModule(module);
				item.setLabel(label);
				item.setId(rs.getLong(1));
				item.setLang(rs.getString(2));
				item.setText(rs.getString(3));
				item.setUpdated(rs.getTimestamp(4));
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
		return item;
	}
	public List getByDateAndLang(Timestamp update,String lang) throws SQLException {
		Connection con = getConnection();
		ArrayList items = new ArrayList();
		try {
			PreparedStatement ps = con.prepareStatement(GET_BY_DATE_AND_LANG);
			ps.setTimestamp(1, update);
			ps.setString(2,lang);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				TransItem item = new TransItem();
				item.setModule(rs.getString("module"));
				item.setLabel(rs.getString("label"));
				item.setId(rs.getLong("id"));
				item.setLang(rs.getString("lang"));
				item.setText(rs.getString("text"));
				item.setUpdated(rs.getTimestamp("updated"));
				item.setSource(rs.getString("source"));
				// System.out.println( item.getLang() + ":" + item.getText() );
				items.add(item);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
		return items;
	}
	public void updateText(TransItem item) throws SQLException {
		Connection con = getConnection();
		try {
			PreparedStatement ps = con.prepareStatement(UPDATE_TEXT);
			ps.setString(1, item.getText());
			ps.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
			ps.setLong(3, item.getId());
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
	}
	public long getLastId() throws SQLException {
		Connection con = getConnection();
		long result = 0;
		try {
			PreparedStatement ps = con
					.prepareStatement("SELECT max(id) FROM iface_trans");
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				result = rs.getLong(1);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
		return result;
	}
	public void delete_all() throws SQLException {
		Connection con = getConnection();
		try {
			PreparedStatement ps = con.prepareStatement(DELETE_ALL_ITEMS);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
	}
	public final static String GET_BY_DATE_AND_LANG = "SELECT id,module,label,lang,text,updated,source FROM iface_trans WHERE updated>? AND lang=?";
	public final static String GET_BY_MODULE = "SELECT id, label, lang, text, updated,source FROM iface_trans WHERE module=?";
	public final static String GET_BY_MODULE_AND_LABEL = "SELECT id, lang, text, updated,source FROM iface_trans WHERE module=? AND label=?";
	public final static String GET_BY_MODULE_LABEL_AND_LANG = "SELECT id, lang, text, updated FROM iface_trans WHERE module=? AND label=? AND lang=?";
	public static final String GET_MODULES = "SELECT DISTINCT module FROM iface_trans";
	public static final String GET_BY_ID = "SELECT module, label, lang, text, updated,source FROM iface_trans WHERE id=?";
	public static final String DELETE_ALL_ITEMS = "DELETE FROM iface_trans";
	public final static String INSERT = "INSERT INTO iface_trans (id,module,label,lang,text,source) "
			+ "VALUES (?,?,?,?,?,?)";
	public final static String UPDATE_TEXT = "UPDATE iface_trans SET text=?,updated=? WHERE id=?";
	public final static String UPDATE_BY_MODULE_LABEL_AND_LANG = "UPDATE iface_trans SET text=?,source=? WHERE module=? AND label=? AND lang=?";
}
